﻿using System;
using Eassets.Layered.Domain.Secure;
using System.Data.SqlClient;
using System.Data;
using System.Collections.ObjectModel;

namespace Eassets.Data.Secure
{
    public class SecurityGroupUserData
    {
        /// <summary>
        /// Inserts securityGroupUser.
        /// </summary>
        /// <param name="securityGroupUser"></param>
        public void AddSecurityGroupUser(Collection<SecurityGroupUser> securityGroupUser)
        {
            SqlCommand command = new SqlCommand("SP_AddSecurityGroupUser"); // Name of the SP
            CommonData commonData = new CommonData();
            commonData.ExecuteNonQuery(ConstructCommand(command, securityGroupUser));
        }

        ///// <summary>
        ///// Updates securityGroupUser.
        ///// </summary>
        ///// <param name="sender"></param>
        ///// <param name="e"></param>
        //public void ModifySecurityGroupUser(SecurityGroupUser securityGroupUser)
        //{
        //    SqlCommand command = new SqlCommand("SP_ModifySecurityGroupUser"); // Name of the SP
        //    command.Parameters.AddWithValue("@SecurityGroupUserId", securityGroupUser.SecurityGroupUserId);
        //     CommonData commonData = new CommonData();commonData.ExecuteNonQuery(ConstructCommand(command, securityGroupUser));
        //}


        /// <summary>
        /// Delete securityGroupUser.
        /// </summary>
        /// <param name="securityGroupUserId"></param>
        public void DeleteSecurityGroupUser(long securityGroupUserId)
        {
            SqlCommand command = new SqlCommand("SP_DeleteSecurityGroupUser"); // Name of the SP
            command.Parameters.AddWithValue("@SecurityGroupUserId", securityGroupUserId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); commonData.ExecuteNonQuery(command);
        }

        /// <summary>
        /// GetbyId securityGroupUser.
        /// </summary>
        /// <param name="securityGroupUserId"></param>
        /// <returns></returns>
        public SecurityGroupUser GetByIdSecurityGroupUser(long securityGroupUserId)
        {
            SqlCommand command = new SqlCommand("SP_GetSecurityGroupUserById");
            command.Parameters.AddWithValue("@SecurityGroupUserId", securityGroupUserId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); SqlDataReader dataReader = commonData.ExecuteReader(command);
            SecurityGroupUser securityGroupUser = new SecurityGroupUser();
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty(dataReader["SecurityGroupUserId"].ToString())))
                {
                    securityGroupUser.SecurityGroupUserId = Convert.ToInt64(dataReader["SecurityGroupUserId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["SecurityGroupId"].ToString())))
                {
                    securityGroupUser.SecurityGroupId = Convert.ToInt64(dataReader["SecurityGroupId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["UserId"].ToString())))
                {
                    securityGroupUser.UserId = Convert.ToInt64(dataReader["UserId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["EffectiveDate"].ToString())))
                {
                    securityGroupUser.EffectiveDate = Convert.ToDateTime(dataReader["EffectiveDate"]);
                }
            }
            dataReader.Close();
            return securityGroupUser;
        }

        /// <summary>
        /// GetAll securityGroupUser.
        /// </summary>
        /// <returns></returns>
        public Collection<SecurityGroupUser> GetAllSecurityGroupUser()
        {
            Collection<SecurityGroupUser> securityGroupUsers = new Collection<SecurityGroupUser>();
            SqlCommand command = new SqlCommand("SP_GetAllSecurityGroupUsers");
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            SecurityGroupUser securityGroupUser;
            while (dataReader.Read())
            {
                securityGroupUser = new SecurityGroupUser();
                if (!(String.IsNullOrEmpty(dataReader["SecurityGroupUserId"].ToString())))
                {
                    securityGroupUser.SecurityGroupUserId = Convert.ToInt64(dataReader["SecurityGroupUserId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["SecurityGroupId"].ToString())))
                {
                    securityGroupUser.SecurityGroupId = Convert.ToInt64(dataReader["SecurityGroupId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["UserId"].ToString())))
                {
                    securityGroupUser.UserId = Convert.ToInt64(dataReader["UserId"]);
                }

                EmployeeData employeeData = new EmployeeData();

                securityGroupUser.Employee = employeeData.GetByIdEmployee(securityGroupUser.UserId);


                if (!(String.IsNullOrEmpty(dataReader["EffectiveDate"].ToString())))
                {
                    securityGroupUser.EffectiveDate = Convert.ToDateTime(dataReader["EffectiveDate"]);
                }

                securityGroupUsers.Add(securityGroupUser);
            }
            dataReader.Close();
            return securityGroupUsers;
        }

        public SecurityGroupUser GetSecurityGroupUserforAccessRightBySecurityGroupAndUserId(long securityGroupId, long userId)
        {
            SqlCommand command = new SqlCommand("SP_GetSecurityGroupUserforAccessRightBySecurityGroupAndUserId");
            command.Parameters.Add("@SecurityGroupId", securityGroupId);
            command.Parameters.Add("@UserId", userId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            SecurityGroupUser securityGroupUser = new SecurityGroupUser();
            while (dataReader.Read())
            {
                securityGroupUser = new SecurityGroupUser();
                if (!(String.IsNullOrEmpty(dataReader["SecurityGroupUserId"].ToString())))
                {
                    securityGroupUser.SecurityGroupUserId = Convert.ToInt64(dataReader["SecurityGroupUserId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["SecurityGroupId"].ToString())))
                {
                    securityGroupUser.SecurityGroupId = Convert.ToInt64(dataReader["SecurityGroupId"]);
                }

            }
            dataReader.Close();
            return securityGroupUser;

        }

        public long GetSecurityGroupIdByUserId(long userId)
        {
            SqlCommand command = new SqlCommand("SP_GetSecurityGroupUserforAccessRight");
            command.Parameters.Add("@UserId", userId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            long securityGroupId = 0;
            while (dataReader.Read())
            {
                securityGroupId = Convert.ToInt64(dataReader["SecurityGroupId"]);
            }
            return securityGroupId;
        }


        //public Collection<long> GetEmpIdFromEmployeeMappedBySecurityGroupUser()
        //{
        //    Collection<long> empIdList = new Collection<long>();
        //    SqlCommand command = new SqlCommand("SP_GetEmpIdFromEmployeeMappedBySecurityGroupUser");
        //    command.CommandType = CommandType.StoredProcedure;
        //    CommonData commonData = new CommonData();
        //    SqlDataReader dataReader = commonData.ExecuteReader(command);
        //    while (dataReader.Read())
        //    {
        //        long id = new long();
        //        if (!(String.IsNullOrEmpty(dataReader["EmployeeId"].ToString())))
        //        {
        //            id = Convert.ToInt64(dataReader["EmployeeId"]);
        //        }
        //        empIdList.Add(id);
        //    }
        //    dataReader.Close();
        //    return empIdList;
        //}

        public Collection<long> GetUnMappedEmpIdsBySecurityGroup(long securityGroupId)
        {
            Collection<long> empIdList = new Collection<long>();
            SqlCommand command = new SqlCommand("SP_GetUnMappedEmpIdsBySecurityGroup");
            command.Parameters.AddWithValue("@SecurityGroupId", securityGroupId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            while (dataReader.Read())
            {
                long id = new long();
                if (!(String.IsNullOrEmpty(dataReader["EmployeeId"].ToString())))
                {
                    id = Convert.ToInt64(dataReader["EmployeeId"]);
                }
                empIdList.Add(id);
            }
            dataReader.Close();
            return empIdList;
        }

        public Collection<long> GetEmpIdFromSecurityGroupUser(long securityGroupId)
        {
            Collection<long> empIdList = new Collection<long>();
            SqlCommand command = new SqlCommand("SP_GetEmpIdFromSecurityGroupUser");
            command.Parameters.AddWithValue("SecurityGroupId", securityGroupId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            while (dataReader.Read())
            {
                long id = new long();
                if (!(String.IsNullOrEmpty(dataReader["UserId"].ToString())))
                {
                    id = Convert.ToInt64(dataReader["UserId"]);
                }

                empIdList.Add(id);
            }
            dataReader.Close();
            return empIdList;
        }

        public long GetSecurityGroupUserIdFromSecurityGroupUser(long employeeId, long securityGroupId)
        {
            long securityGroupUserId = 0;
            SqlCommand command = new SqlCommand("SP_GetSecurityGroupUserIdFromSecurityGroupUser");
            command.Parameters.AddWithValue("@EmployeeId", employeeId);
            command.Parameters.AddWithValue("@SecurityGroupId", securityGroupId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty(dataReader["SecurityGroupUserId"].ToString())))
                {
                    securityGroupUserId = Convert.ToInt64(dataReader["SecurityGroupUserId"]);
                }
            }
            return securityGroupUserId;
        }

        public SqlCommand ConstructCommand(SqlCommand command, Collection<SecurityGroupUser> securityGroupUsers)
        {
            command.CommandType = CommandType.StoredProcedure;

            foreach (SecurityGroupUser securityGroupUser in securityGroupUsers)
            {
                try
                {
                    command.Parameters.AddWithValue("@SecurityGroupId", securityGroupUser.SecurityGroupId);
                }
                catch (Exception)
                { }
                try
                {
                    command.Parameters.AddWithValue("@UserId", securityGroupUser.UserId);
                }
                catch (Exception)
                { }

                try
                {
                    command.Parameters.AddWithValue("@EffectiveDate", securityGroupUser.EffectiveDate);
                }
                catch (Exception)
                { }

            }

            return command;
        }
    }
}

